查詢語法建構器 ¶

查詢語法建構器建立於資料庫存取物件之上，讓您能以程式化且與 DBMS 無關的方式建構 SQL 查詢。相較於撰寫原始 SQL 陳述式，使用查詢語法建構器將有助於您撰寫更易讀的 SQL 相關程式碼，並產生更安全的 SQL 陳述式。

使用查詢語法建構器通常包含兩個步驟

1. 建立 yii\db\Query 物件以表示 SELECT SQL 陳述式的不同部分（例如 SELECT、FROM）。
2. 執行 yii\db\Query 的查詢方法（例如 all()）以從資料庫檢索資料。

以下程式碼示範了使用查詢語法建構器的典型方式

$rows = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->limit(10)
    ->all();

上述程式碼產生並執行下列 SQL 查詢，其中 :last_name 參數與字串 'Smith' 繫結。

SELECT `id`, `email` 
FROM `user`
WHERE `last_name` = :last_name
LIMIT 10

資訊：您通常主要使用 yii\db\Query 而不是 yii\db\QueryBuilder。yii\db\QueryBuilder 在您呼叫其中一個查詢方法時，會由前者隱式調用。yii\db\QueryBuilder 類別負責從與 DBMS 無關的 yii\db\Query 物件產生與 DBMS 相關的 SQL 陳述式（例如，以不同方式引用資料表/欄位名稱）。

建構查詢 ¶

若要建構 yii\db\Query 物件，您可以呼叫不同的查詢建構方法來指定 SQL 查詢的不同部分。這些方法的名稱類似於 SQL 陳述式對應部分中使用的 SQL 關鍵字。例如，若要指定 SQL 查詢的 FROM 部分，您可以呼叫 from() 方法。所有查詢建構方法都會傳回查詢物件本身，讓您可以將多個呼叫鏈結在一起。

在以下章節中，我們將說明每個查詢建構方法的使用方式。

select() ¶

select() 方法指定 SQL 陳述式的 SELECT 片段。您可以陣列或字串形式指定要選取的欄位，如下所示。當從查詢物件產生 SQL 陳述式時，選取的欄位名稱將會自動被引用。

$query->select(['id', 'email']);

// equivalent to:

$query->select('id, email');

選取的欄位名稱可能包含資料表前綴和/或欄位別名，就像您撰寫原始 SQL 查詢時一樣。例如，

$query->select(['user.id AS user_id', 'email']);

// equivalent to:

$query->select('user.id AS user_id, email');

如果您使用陣列格式指定欄位，您也可以使用陣列鍵來指定欄位別名。例如，上述程式碼可以改寫如下，

$query->select(['user_id' => 'user.id', 'email']);

如果您在建構查詢時未呼叫 select() 方法，則會選取 *，這表示選取所有欄位。

除了欄位名稱之外，您還可以選取資料庫表達式。當選取包含逗號的資料庫表達式時，您必須使用陣列格式，以避免不正確的自動名稱引用。例如，

$query->select(["CONCAT(first_name, ' ', last_name) AS full_name", 'email']); 

與所有涉及原始 SQL 的地方一樣，您可以在 select 中撰寫資料庫表達式時，使用與 DBMS 無關的引用語法來表示資料表和欄位名稱。

從 2.0.1 版開始，您也可以選取子查詢。您應該以 yii\db\Query 物件的形式指定每個子查詢。例如，

$subQuery = (new Query())->select('COUNT(*)')->from('user');

// SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `post`
$query = (new Query())->select(['id', 'count' => $subQuery])->from('post');

若要選取不同的列，您可以呼叫 distinct()，如下所示

// SELECT DISTINCT `user_id` ...
$query->select('user_id')->distinct();

您可以呼叫 addSelect() 來選取其他欄位。例如，

$query->select(['id', 'username'])
    ->addSelect(['email']);

from() ¶

from() 方法指定 SQL 陳述式的 FROM 片段。例如，

// SELECT * FROM `user`
$query->from('user');

您可以字串或陣列形式指定要從中選取的資料表。資料表名稱可以包含結構描述前綴和/或資料表別名，就像您撰寫原始 SQL 陳述式時一樣。例如，

$query->from(['public.user u', 'public.post p']);

// equivalent to:

$query->from('public.user u, public.post p');

如果您使用陣列格式，您也可以使用陣列鍵來指定資料表別名，如下所示

$query->from(['u' => 'public.user', 'p' => 'public.post']);

除了資料表名稱之外，您也可以從子查詢中選取，方法是以 yii\db\Query 物件的形式指定子查詢。例如，

$subQuery = (new Query())->select('id')->from('user')->where('status=1');

// SELECT * FROM (SELECT `id` FROM `user` WHERE status=1) u 
$query->from(['u' => $subQuery]);

前綴 ¶

也可以套用預設的 tablePrefix。實作指示在「資料庫存取物件」指南的「引用資料表」章節中。

where() ¶

where() 方法指定 SQL 查詢的 WHERE 片段。您可以使用四種格式之一來指定 WHERE 條件

• 字串格式，例如 'status=1'
• 雜湊格式，例如 ['status' => 1, 'type' => 2]
• 運算子格式，例如 ['like', 'name', 'test']
• 物件格式，例如 new LikeCondition('name', 'LIKE', 'test')

字串格式 ¶

字串格式最適合用於指定非常簡單的條件，或者如果您需要使用 DBMS 的內建函式。它的運作方式就像您正在撰寫原始 SQL 一樣。例如，

$query->where('status=1');

// or use parameter binding to bind dynamic parameter values
$query->where('status=:status', [':status' => $status]);

// raw SQL using MySQL YEAR() function on a date field
$query->where('YEAR(somedate) = 2015');

請勿像以下範例一樣直接將變數嵌入條件中，尤其是當變數值來自終端使用者輸入時，因為這會使您的應用程式容易受到 SQL 注入攻擊。

// Dangerous! Do NOT do this unless you are very certain $status must be an integer.
$query->where("status=$status");

當使用 參數繫結 時，您可以呼叫 params() 或 addParams() 來個別指定參數。

$query->where('status=:status')
    ->addParams([':status' => $status]);

與所有涉及原始 SQL 的地方一樣，您可以在字串格式中撰寫條件時，使用與 DBMS 無關的引用語法來表示資料表和欄位名稱。

雜湊格式 ¶

雜湊格式最適合用於指定多個 AND 連接的子條件，每個子條件都是簡單的相等性斷言。它寫成一個陣列，其鍵為欄位名稱，值為欄位應有的對應值。例如，

// ...WHERE (`status` = 10) AND (`type` IS NULL) AND (`id` IN (4, 8, 15))
$query->where([
    'status' => 10,
    'type' => null,
    'id' => [4, 8, 15],
]);

如您所見，查詢語法建構器非常聰明，足以正確處理空值或陣列的值。

您也可以將子查詢與雜湊格式一起使用，如下所示

$userQuery = (new Query())->select('id')->from('user');

// ...WHERE `id` IN (SELECT `id` FROM `user`)
$query->where(['id' => $userQuery]);

使用雜湊格式，Yii 內部會對值套用參數繫結，因此與字串格式相反，在此您不必手動新增參數。但是，請注意，Yii 永遠不會逸出欄位名稱，因此如果您將從使用者端取得的變數作為欄位名稱傳遞，而沒有任何額外檢查，則應用程式將容易受到 SQL 注入攻擊。為了保持應用程式安全，請不要使用變數作為欄位名稱，或根據允許清單篩選變數。如果您需要從使用者取得欄位名稱，請閱讀篩選資料指南文章。例如，以下程式碼容易受到攻擊

// Vulnerable code:
$column = $request->get('column');
$value = $request->get('value');
$query->where([$column => $value]);
// $value is safe, but $column name won't be encoded!

運算子格式 ¶

運算子格式可讓您以程式化方式指定任意條件。它採用以下格式

[operator, operand1, operand2, ...]

其中運算元可以遞迴地以字串格式、雜湊格式或運算子格式指定，而運算子可以是以下之一

• and：運算元應使用 AND 連接在一起。例如，['and', 'id=1', 'id=2'] 將產生 id=1 AND id=2。如果運算元是陣列，則會使用此處描述的規則將其轉換為字串。例如，['and', 'type=1', ['or', 'id=1', 'id=2']] 將產生 type=1 AND (id=1 OR id=2)。此方法不會執行任何引用或逸出。

• or：與 and 運算子類似，不同之處在於運算元使用 OR 連接。

• not：只需要運算元 1，它將被包裝在 NOT() 中。例如，['not', 'id=1'] 將產生 NOT (id=1)。運算元 1 也可以是陣列，以描述多個表達式。例如，['not', ['status' => 'draft', 'name' => 'example']] 將產生 NOT ((status='draft') AND (name='example'))。

• between：運算元 1 應該是欄位名稱，而運算元 2 和 3 應該是欄位所在範圍的起始值和結束值。例如，['between', 'id', 1, 10] 將產生 id BETWEEN 1 AND 10。如果您需要建構一個條件，其中值介於兩個欄位之間（例如 11 BETWEEN min_id AND max_id），您應該使用 BetweenColumnsCondition。請參閱條件 – 物件格式章節，以深入瞭解條件的物件定義。

• not between：與 between 類似，不同之處在於產生的條件中，BETWEEN 會替換為 NOT BETWEEN。

• in：運算元 1 應該是欄位或資料庫表達式。運算元 2 可以是陣列或 Query 物件。它將產生 IN 條件。如果運算元 2 是陣列，則它將表示欄位或資料庫表達式應有的值範圍；如果運算元 2 是 Query 物件，則會產生子查詢並用作欄位或資料庫表達式的範圍。例如，['in', 'id', [1, 2, 3]] 將產生 id IN (1, 2, 3)。此方法將正確引用欄位名稱並逸出範圍中的值。in 運算子也支援複合欄位。在這種情況下，運算元 1 應該是欄位的陣列，而運算元 2 應該是陣列的陣列或 Query 物件，表示欄位的範圍。例如，['in', ['id', 'name'], [['id' => 1, 'name' => 'oy']]] 將產生 (id, name) IN ((1, 'oy'))。

• not in：與 in 運算子類似，不同之處在於產生的條件中，IN 會替換為 NOT IN。

• like：運算元 1 應該是欄位或資料庫表達式，而運算元 2 應該是字串或陣列，表示欄位或資料庫表達式應有的值。例如，['like', 'name', 'tester'] 將產生 name LIKE '%tester%'。當值範圍以陣列形式給定時，將產生多個 LIKE 述詞，並使用 AND 連接。例如，['like', 'name', ['test', 'sample']] 將產生 name LIKE '%test%' AND name LIKE '%sample%'。您也可以提供選用的第三個運算元，以指定如何逸出值中的特殊字元。運算元應該是從特殊字元到其逸出對應項的對應陣列。如果未提供此運算元，則將使用預設逸出對應。您可以使用 false 或空陣列來指示值已逸出，且不應套用逸出。請注意，當使用逸出對應（或未提供第三個運算元）時，值將自動包含在一對百分比字元中。

  注意：當使用 PostgreSQL 時，您也可以使用 ilike 而不是 like 進行不區分大小寫的比對。

• or like：與 like 運算子類似，不同之處在於當運算元 2 是陣列時，OR 用於連接 LIKE 述詞。

• not like：與 like 運算子類似，不同之處在於產生的條件中，LIKE 會替換為 NOT LIKE。

• or not like：與 not like 運算子類似，不同之處在於 OR 用於連接 NOT LIKE 述詞。

• exists：需要一個運算元，該運算元必須是 yii\db\Query 的實例，表示子查詢。它將建構 EXISTS (子查詢) 表達式。

• not exists：與 exists 運算子類似，並建構 NOT EXISTS (子查詢) 表達式。

• >、<= 或任何其他接受兩個運算元的有效資料庫運算子：第一個運算元必須是欄位名稱，而第二個運算元是值。例如，['>', 'age', 10] 將產生 age>10。

使用運算子格式，Yii 內部會對值使用參數繫結，因此與字串格式相反，在此您不必手動新增參數。但是，請注意，Yii 永遠不會逸出欄位名稱，因此如果您將變數作為欄位名稱傳遞，則應用程式很可能會容易受到 SQL 注入攻擊。為了保持應用程式安全，請不要使用變數作為欄位名稱，或根據允許清單篩選變數。如果您需要從使用者取得欄位名稱，請閱讀篩選資料指南文章。例如，以下程式碼容易受到攻擊

// Vulnerable code:
$column = $request->get('column');
$value = $request->get('value');
$query->where(['=', $column, $value]);
// $value is safe, but $column name won't be encoded!

物件格式 ¶

物件格式自 2.0.14 版起可用，是定義條件的最強大且最複雜的方式。如果您想要在查詢語法建構器之上建立自己的抽象概念，或者如果您想要實作自己的複雜條件，則需要遵循它。

條件類別的實例是不可變的。它們的唯一目的是儲存條件資料並為條件建構器提供 getter。條件建構器是一個類別，其中包含將條件中儲存的資料轉換為 SQL 表達式的邏輯。

在內部，上述格式在建構原始 SQL 之前會隱式轉換為物件格式，因此可以在單一條件中組合格式

$query->andWhere(new OrCondition([
    new InCondition('type', 'in', $types),
    ['like', 'name', '%good%'],
    'disabled=false'
]))

從運算子格式轉換為物件格式是根據 QueryBuilder::conditionClasses 屬性執行的，該屬性將運算子名稱對應到代表性類別名稱

• AND、OR -> yii\db\conditions\ConjunctionCondition
• NOT -> yii\db\conditions\NotCondition
• IN、NOT IN -> yii\db\conditions\InCondition
• BETWEEN、NOT BETWEEN -> yii\db\conditions\BetweenCondition

依此類推。

使用物件格式可以建立您自己的條件或變更預設條件的建構方式。請參閱新增自訂條件和表達式章節以瞭解更多資訊。

附加條件 ¶

您可以使用 andWhere() 或 orWhere() 將其他條件附加到現有條件。您可以多次呼叫它們以分別附加多個條件。例如，

$status = 10;
$search = 'yii';

$query->where(['status' => $status]);

if (!empty($search)) {
    $query->andWhere(['like', 'title', $search]);
}

如果 $search 不是空的，則將產生以下 WHERE 條件

WHERE (`status` = 10) AND (`title` LIKE '%yii%')

篩選條件 ¶

當根據終端使用者的輸入建構 WHERE 條件時，您通常會想要忽略那些為空的輸入值。例如，在允許您依使用者名稱和電子郵件搜尋的搜尋表單中，如果使用者未在使用者名稱/電子郵件輸入欄位中輸入任何內容，您會想要忽略使用者名稱/電子郵件條件。您可以使用 filterWhere() 方法來達成此目標

// $username and $email are from user inputs
$query->filterWhere([
    'username' => $username,
    'email' => $email,
]);

filterWhere() 和 where() 之間的唯一區別在於，前者將忽略在雜湊格式條件中提供的空值。因此，如果 $email 為空而 $username 不為空，則上述程式碼將產生 SQL 條件 WHERE username=:username。

資訊：如果值為 null、空陣列、空字串或僅包含空格的字串，則會將其視為空值。

與 andWhere() 和 orWhere() 類似，您可以使用 andFilterWhere() 和 orFilterWhere() 將其他篩選條件附加到現有條件。

此外，還有 yii\db\Query::andFilterCompare()，它可以根據值中的內容智慧地判斷運算子

$query->andFilterCompare('name', 'John Doe');
$query->andFilterCompare('rating', '>9');
$query->andFilterCompare('value', '<=100');

您也可以明確指定運算子

$query->andFilterCompare('name', 'Doe', 'like');

自 Yii 2.0.11 起，HAVING 條件有類似的方法

• filterHaving()
• andFilterHaving()
• orFilterHaving()

orderBy() ¶

orderBy() 方法指定 SQL 查詢的 ORDER BY 片段。例如，

// ... ORDER BY `id` ASC, `name` DESC
$query->orderBy([
    'id' => SORT_ASC,
    'name' => SORT_DESC,
]);

在上述程式碼中，陣列鍵是欄位名稱，而陣列值是對應的排序方向。PHP 常數 SORT_ASC 指定遞增排序，而 SORT_DESC 指定遞減排序。

如果 ORDER BY 僅涉及簡單的欄位名稱，則您可以使用字串來指定它，就像您撰寫原始 SQL 陳述式時一樣。例如，

$query->orderBy('id ASC, name DESC');

注意：如果 ORDER BY 涉及某些資料庫表達式，則應使用陣列格式。

您可以呼叫 addOrderBy() 將其他欄位新增至 ORDER BY 片段。例如，

$query->orderBy('id ASC')
    ->addOrderBy('name DESC');

groupBy() ¶

groupBy() 方法指定 SQL 查詢的 GROUP BY 片段。例如，

// ... GROUP BY `id`, `status`
$query->groupBy(['id', 'status']);

如果 GROUP BY 僅涉及簡單的欄位名稱，則您可以使用字串來指定它，就像您撰寫原始 SQL 陳述式時一樣。例如，

$query->groupBy('id, status');

注意：如果 GROUP BY 涉及某些資料庫表達式，則應使用陣列格式。

您可以呼叫 addGroupBy() 將其他欄位新增至 GROUP BY 片段。例如，

$query->groupBy(['id', 'status'])
    ->addGroupBy('age');

having() ¶

having() 方法指定 SQL 查詢的 HAVING 片段。它採用與where() 相同的條件指定方式。例如，

// ... HAVING `status` = 1
$query->having(['status' => 1]);

請參閱where() 的文件，以瞭解有關如何指定條件的更多詳細資訊。

您可以呼叫 andHaving() 或 orHaving() 將其他條件附加到 HAVING 片段。例如，

// ... HAVING (`status` = 1) AND (`age` > 30)
$query->having(['status' => 1])
    ->andHaving(['>', 'age', 30]);

limit() 和 offset() ¶

limit() 和 offset() 方法指定 SQL 查詢的 LIMIT 和 OFFSET 片段。例如，

// ... LIMIT 10 OFFSET 20
$query->limit(10)->offset(20);

如果您指定無效的 limit 或 offset（例如，負值），則會將其忽略。

資訊：對於不支援 LIMIT 和 OFFSET 的 DBMS（例如 MSSQL），查詢語法建構器將產生一個模擬 LIMIT/OFFSET 行為的 SQL 陳述式。

join() ¶

join() 方法指定 SQL 查詢的 JOIN 片段。例如，

// ... LEFT JOIN `post` ON `post`.`user_id` = `user`.`id`
$query->join('LEFT JOIN', 'post', 'post.user_id = user.id');

join() 方法接受四個參數

• $type：聯結類型，例如 'INNER JOIN'、'LEFT JOIN'。
• $table：要聯結的資料表名稱。
• $on：選用，聯結條件，即 ON 片段。請參閱where() 以瞭解有關指定條件的詳細資訊。請注意，陣列語法不適用於指定基於欄位的條件，例如 ['user.id' => 'comment.userId'] 將產生一個條件，其中使用者 ID 必須等於字串 'comment.userId'。您應該改用字串語法，並將條件指定為 'user.id = comment.userId'。
• $params：選用，要繫結到聯結條件的參數。

您可以使用以下快速鍵方法分別指定 INNER JOIN、LEFT JOIN 和 RIGHT JOIN。

• innerJoin()
• leftJoin()
• rightJoin()

例如，

$query->leftJoin('post', 'post.user_id = user.id');

若要與多個資料表聯結，請針對每個資料表多次呼叫上述聯結方法。

除了與資料表聯結之外，您還可以與子查詢聯結。若要執行此操作，請將要聯結的子查詢指定為 yii\db\Query 物件。例如，

$subQuery = (new \yii\db\Query())->from('post');
$query->leftJoin(['u' => $subQuery], 'u.id = author_id');

在這種情況下，您應該將子查詢放在陣列中，並使用陣列鍵來指定別名。

union() ¶

union() 方法指定 SQL 查詢的 UNION 片段。例如，

$query1 = (new \yii\db\Query())
    ->select("id, category_id AS type, name")
    ->from('post')
    ->limit(10);

$query2 = (new \yii\db\Query())
    ->select('id, type, name')
    ->from('user')
    ->limit(10);

$query1->union($query2);

您可以多次呼叫 union() 以附加更多 UNION 片段。

withQuery() ¶

withQuery() 方法指定 SQL 查詢的 WITH 前綴。您可以使用它來代替子查詢，以提高可讀性和某些獨特功能（遞迴 CTE）。在 modern-sql 中閱讀更多內容。例如，此查詢將遞迴選取 admin 的所有巢狀權限及其子項，

$initialQuery = (new \yii\db\Query())
    ->select(['parent', 'child'])
    ->from(['aic' => 'auth_item_child'])
    ->where(['parent' => 'admin']);

$recursiveQuery = (new \yii\db\Query())
    ->select(['aic.parent', 'aic.child'])
    ->from(['aic' => 'auth_item_child'])
    ->innerJoin('t1', 't1.child = aic.parent');

$mainQuery = (new \yii\db\Query())
    ->select(['parent', 'child'])
    ->from('t1')
    ->withQuery($initialQuery->union($recursiveQuery), 't1', true);

可以多次呼叫 withQuery() 以將更多 CTE 前置到主查詢。查詢將以與附加順序相同的順序前置。如果其中一個查詢是遞迴的，則整個 CTE 將變為遞迴。

查詢方法 ¶

yii\db\Query 為不同的查詢目的提供了一整套方法

• all()：傳回列陣列，其中每列都是名稱-值配對的關聯陣列。
• one()：傳回結果的第一列。
• column()：傳回結果的第一欄。
• scalar()：傳回位於結果的第一列和第一欄的純量值。
• exists()：傳回一個值，指示查詢是否包含任何結果。
• count()：傳回 COUNT 查詢的結果。
• 其他彙總查詢方法，包括 sum($q)、average($q)、max($q)、min($q)。$q 參數對於這些方法是強制性的，可以是欄位名稱或資料庫表達式。

例如，

// SELECT `id`, `email` FROM `user`
$rows = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->all();
    
// SELECT * FROM `user` WHERE `username` LIKE `%test%`
$row = (new \yii\db\Query())
    ->from('user')
    ->where(['like', 'username', 'test'])
    ->one();

注意： one() 方法僅會回傳查詢結果的第一列資料。它「不會」在產生的 SQL 語句中加入 LIMIT 1。如果您知道查詢只會回傳一列或少數幾列資料（例如，如果您使用一些主鍵進行查詢），這樣做是沒問題且較好的。但是，如果查詢可能產生大量的資料列，您應該明確地呼叫 limit(1) 以提升效能，例如：(new \yii\db\Query())->from('user')->limit(1)->one()。

所有這些查詢方法都接受一個可選的 $db 參數，代表應該用於執行資料庫查詢的 DB 連線。如果您省略此參數，則會使用 db 應用程式元件 作為 DB 連線。以下是另一個使用 count() 查詢方法的範例

// executes SQL: SELECT COUNT(*) FROM `user` WHERE `last_name`=:last_name
$count = (new \yii\db\Query())
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->count();

當您呼叫 yii\db\Query 的查詢方法時，它實際上會在內部執行以下工作

• 呼叫 yii\db\QueryBuilder 以根據 yii\db\Query 的目前結構產生 SQL 語句；
• 使用產生的 SQL 語句建立 yii\db\Command 物件；
• 呼叫 yii\db\Command 的查詢方法（例如 queryAll()）以執行 SQL 語句並檢索資料。

有時，您可能想要檢查或使用從 yii\db\Query 物件建構的 SQL 語句。您可以使用以下程式碼來達成此目的

$command = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->limit(10)
    ->createCommand();
    
// show the SQL statement
echo $command->sql;
// show the parameters to be bound
print_r($command->params);

// returns all rows of the query result
$rows = $command->queryAll();

索引查詢結果 ¶

當您呼叫 all() 時，它會回傳一個資料列陣列，這些資料列會以連續整數作為索引。有時您可能想要以不同的方式索引它們，例如以特定的欄位或運算式值作為索引。您可以透過在 all() 之前呼叫 indexBy() 來達成此目的。例如：

// returns [100 => ['id' => 100, 'username' => '...', ...], 101 => [...], 103 => [...], ...]
$query = (new \yii\db\Query())
    ->from('user')
    ->limit(10)
    ->indexBy('id')
    ->all();

傳遞到 indexBy() 方法中的欄位名稱必須存在於結果集中，索引才能運作 - 這取決於開發人員來處理。

若要依運算式值進行索引，請將匿名函式傳遞給 indexBy() 方法

$query = (new \yii\db\Query())
    ->from('user')
    ->indexBy(function ($row) {
        return $row['id'] . $row['username'];
    })->all();

匿名函式接受一個參數 $row，其中包含目前的資料列資料，並且應該回傳一個純量值，該值將用作目前資料列的索引值。

注意： 與 groupBy() 或 orderBy() 等查詢方法不同，後者會轉換為 SQL 並且是查詢的一部分，此方法在從資料庫中提取資料後才運作。這表示只能使用那些已包含在您的查詢的 SELECT 中的欄位名稱。此外，如果您使用資料表前綴選取了一個欄位，例如 customer.id，則結果集只會包含 id，因此您必須呼叫 ->indexBy('id')，而沒有資料表前綴。

批次查詢 ¶

當處理大量資料時，諸如 yii\db\Query::all() 等方法並不適用，因為它們需要將整個查詢結果載入到用戶端的記憶體中。為了解決這個問題，Yii 提供了批次查詢支援。伺服器保存查詢結果，而用戶端使用游標一次迭代結果集的一個批次。

警告： 對於 MySQL 批次查詢的實作，存在已知的限制和變通方法。請參閱下方。

批次查詢可以像下面這樣使用

use yii\db\Query;

$query = (new Query())
    ->from('user')
    ->orderBy('id');

foreach ($query->batch() as $users) {
    // $users is an array of 100 or fewer rows from the user table
}

// or to iterate the row one by one
foreach ($query->each() as $user) {
    // data is being fetched from the server in batches of 100,
    // but $user represents one row of data from the user table
}

yii\db\Query::batch() 和 yii\db\Query::each() 方法會回傳一個 yii\db\BatchQueryResult 物件，該物件實作了 Iterator 介面，因此可以在 foreach 結構中使用。在第一次迭代期間，會對資料庫進行 SQL 查詢。然後在剩餘的迭代中以批次方式提取資料。預設情況下，批次大小為 100，表示每次批次會提取 100 列資料。您可以透過將第一個參數傳遞給 batch() 或 each() 方法來變更批次大小。

與 yii\db\Query::all() 相比，批次查詢一次只將 100 列資料載入到記憶體中。

如果您透過 yii\db\Query::indexBy() 指定要依某些欄位索引查詢結果，批次查詢仍將保留正確的索引。

例如

$query = (new \yii\db\Query())
    ->from('user')
    ->indexBy('username');

foreach ($query->batch() as $users) {
    // $users is indexed by the "username" column
}

foreach ($query->each() as $username => $user) {
    // ...
}

MySQL 中批次查詢的限制 ¶

MySQL 批次查詢的實作依賴於 PDO 驅動程式程式庫。預設情況下，MySQL 查詢是 buffered（緩衝的）。這破壞了使用游標來取得資料的目的，因為它並不能阻止整個結果集被驅動程式載入到用戶端的記憶體中。

注意： 當使用 libmysqlclient（PHP5 的典型情況）時，PHP 的記憶體限制不會計算結果集使用的記憶體。批次查詢看起來似乎運作正常，但實際上整個資料集都被載入到用戶端的記憶體中，並且有可能耗盡記憶體。

若要停用緩衝並減少用戶端記憶體需求，PDO 連線屬性 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 必須設定為 false。但是，在檢索到整個資料集之前，無法透過同一個連線進行其他查詢。這可能會阻止 ActiveRecord 在需要時查詢資料表結構描述。如果這不是問題（資料表結構描述已經快取），則可以將原始連線切換到非緩衝模式，然後在批次查詢完成時回滾。

Yii::$app->db->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

// Do batch query

Yii::$app->db->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

注意： 在 MyISAM 的情況下，在批次查詢期間，資料表可能會被鎖定，從而延遲或拒絕其他連線的寫入存取。當使用非緩衝查詢時，請盡可能縮短游標開啟的時間。

如果結構描述未快取，或者在處理批次查詢時需要執行其他查詢，您可以建立一個單獨的非緩衝連線到資料庫

$unbufferedDb = new \yii\db\Connection([
    'dsn' => Yii::$app->db->dsn,
    'username' => Yii::$app->db->username,
    'password' => Yii::$app->db->password,
    'charset' => Yii::$app->db->charset,
]);
$unbufferedDb->open();
$unbufferedDb->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

如果您想要確保 $unbufferedDb 具有與原始緩衝 $db 完全相同的 PDO 屬性，但 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 為 false，請考慮對 $db 進行深層複製，並手動將其設定為 false。

然後，正常建立查詢。新的連線用於執行批次查詢，並以批次或逐個方式檢索結果

// getting data in batches of 1000
foreach ($query->batch(1000, $unbufferedDb) as $users) {
    // ...
}


// data is fetched from server in batches of 1000, but is iterated one by one 
foreach ($query->each(1000, $unbufferedDb) as $user) {
    // ...
}

當不再需要連線且已檢索到結果集時，可以關閉連線

$unbufferedDb->close();

注意： 非緩衝查詢在 PHP 端使用較少的記憶體，但可能會增加 MySQL 伺服器的負載。建議根據您的生產實務為超大量資料設計您自己的程式碼，例如，劃分整數索引鍵的範圍，並使用非緩衝查詢迴圈它們。

新增自訂條件和運算式 ¶

正如在 條件 – 物件格式 章節中提到的，可以建立自訂條件類別。例如，讓我們建立一個條件，用於檢查特定欄位是否小於某個值。使用運算子格式，它看起來會像下面這樣

[
    'and',
    ['>', 'posts', $minLimit],
    ['>', 'comments', $minLimit],
    ['>', 'reactions', $minLimit],
    ['>', 'subscriptions', $minLimit]
]

當這種條件應用一次時，沒有問題。如果它在單個查詢中多次使用，則可以進行大量最佳化。讓我們建立一個自訂條件物件來示範它。

Yii 有一個 ConditionInterface，必須用於標記代表條件的類別。它需要實作 fromArrayDefinition() 方法，以便可以從陣列格式建立條件。如果您不需要它，您可以使用拋出例外的方式實作此方法。

由於我們建立了自訂條件類別，因此我們可以建構最適合我們任務的 API。

namespace app\db\conditions;

class AllGreaterCondition implements \yii\db\conditions\ConditionInterface
{
    private $columns;
    private $value;

    /**
     * @param string[] $columns Array of columns that must be greater, than $value
     * @param mixed $value the value to compare each $column against.
     */
    public function __construct(array $columns, $value)
    {
        $this->columns = $columns;
        $this->value = $value;
    }
    
    public static function fromArrayDefinition($operator, $operands)
    {
        throw new InvalidArgumentException('Not implemented yet, but we will do it later');
    }
    
    public function getColumns() { return $this->columns; }
    public function getValue() { return $this->vaule; }
}

因此我們可以建立一個條件物件

$condition = new AllGreaterCondition(['col1', 'col2'], 42);

但是 QueryBuilder 仍然不知道如何從這個物件建立 SQL 條件。現在我們需要為這個條件建立一個建構器。它必須實作 yii\db\ExpressionBuilderInterface，這要求我們實作一個 build() 方法。

namespace app\db\conditions;

class AllGreaterConditionBuilder implements \yii\db\ExpressionBuilderInterface
{
    use \yii\db\ExpressionBuilderTrait; // Contains constructor and `queryBuilder` property.

    /**
     * @param ExpressionInterface $condition the condition to be built
     * @param array $params the binding parameters.
     * @return AllGreaterCondition
     */ 
    public function build(ExpressionInterface $expression, array &$params = [])
    {
        $value = $condition->getValue();
        
        $conditions = [];
        foreach ($expression->getColumns() as $column) {
            $conditions[] = new SimpleCondition($column, '>', $value);
        }

        return $this->queryBuilder->buildCondition(new AndCondition($conditions), $params);
    }
}

然後簡單地讓 QueryBuilder 知道我們的新條件 – 將其對應新增到 expressionBuilders 陣列。這可以直接從應用程式配置中完成

'db' => [
    'class' => 'yii\db\mysql\Connection',
    // ...
    'queryBuilder' => [
        'expressionBuilders' => [
            'app\db\conditions\AllGreaterCondition' => 'app\db\conditions\AllGreaterConditionBuilder',
        ],
    ],
],

現在我們可以在 where() 中使用我們的條件

$query->andWhere(new AllGreaterCondition(['posts', 'comments', 'reactions', 'subscriptions'], $minValue));

如果我們想要讓可以使用運算子格式建立我們的自訂條件，我們應該在 QueryBuilder::conditionClasses 中宣告它

'db' => [
    'class' => 'yii\db\mysql\Connection',
    // ...
    'queryBuilder' => [
        'expressionBuilders' => [
            'app\db\conditions\AllGreaterCondition' => 'app\db\conditions\AllGreaterConditionBuilder',
        ],
        'conditionClasses' => [
            'ALL>' => 'app\db\conditions\AllGreaterCondition',
        ],
    ],
],

並在 app\db\conditions\AllGreaterCondition 中建立 AllGreaterCondition::fromArrayDefinition() 方法的實際實作

namespace app\db\conditions;

class AllGreaterCondition implements \yii\db\conditions\ConditionInterface
{
    // ... see the implementation above
     
    public static function fromArrayDefinition($operator, $operands)
    {
        return new static($operands[0], $operands[1]);
    }
}

在那之後，我們可以使用更簡短的運算子格式建立我們的自訂條件

$query->andWhere(['ALL>', ['posts', 'comments', 'reactions', 'subscriptions'], $minValue]);

您可能會注意到，使用了兩個概念：運算式和條件。有一個 yii\db\ExpressionInterface 應該用於標記需要 Expression Builder 類別的物件，該類別實作 yii\db\ExpressionBuilderInterface 以進行建構。還有一個 yii\db\condition\ConditionInterface，它擴展了 ExpressionInterface，並且應該用於可以從陣列定義建立的物件，如上所示，但也需要建構器。

總結

• 運算式 – 是資料集的一個資料傳輸物件 (DTO)，可以以某種方式編譯為一些 SQL 語句（運算子、字串、陣列、JSON 等）。
• 條件 – 是運算式的超集，它聚合了可以編譯為單個 SQL 條件的多個運算式（或純量值）。

您可以建立自己的類別來實作 ExpressionInterface，以隱藏將資料轉換為 SQL 語句的複雜性。您將在下一篇文章中了解更多關於運算式的其他範例；